前言

前面我們介紹了Data Cube、維度表和事實表的概念，今天我們用一個實際的案例來說明與比較 OLTP 和 OLAP 的轉換，也順便複習一下 MySQL 的正規化設計。

案例-數位學習平台

老樣子，我們用數位學習平台作為討論的案例（這個案例已做簡化與轉換，和本公司設計不同）

在數位學習平台上，學生會不定時的收到作業，並且自己可以在任意的時間開始做作業，
開始以後系統會讓學生依序作答每一個題目，直到答對所有題目，就能按下完成作業送出，
系統會自動做批改、計算得分。

為了幫助說明，我們簡化一些情境: 
1. 題目只有選擇題，每一題只有四個選項。
2. 題目會有對應的科目、章節，忽略國中國小還會有冊次的資料。
3. 每份作業裡面只會有一種科目的題目，不會混出。
4. 不討論老師和班級。

交易資料表設計(OLTP)

ERD

ERD 描述了幾個比較核心的關係，為了簡化表達，所以沒有把屬性放進來，等等會直接呈現在資料表上。其中比較特別值得討論的是『作答記錄』，這個設計比較複雜，可以把作答記錄視為一個實體，也可以作為一個三元關係，沒有標準答案。

1. 一個科目裡面會有多個章節，但一個章節只會屬於一個科目
2. 一個題目只會屬於一個章節（簡化），但一個章節裡面會有很多題目
3. 作業裡面會有多道題目，題目也可以被指派到各個不同的作業裡
4. 學生會收到很多作業，作業也可以指派給多位學生
5. 學生會針對收到的作業，每一道題目作答，屬於多對多對多的關係

關聯資料表

完成 ERD以後，轉換成關聯式的資料表，順便複習一下轉換的邏輯：

1. 一對一關係：任選一個實體，把另一個實體的主鍵加入第一個實體的資料表中。
2. 一對多關係：將一的實體主鍵放到多的實體資料表上。
3. 多對多關係：額外增加一張關聯資料表，將兩個實體的主鍵同時放到新的表上，組合成為超級鍵。

這邊圖避免混亂，我就先不加上外部鍵了。

OLTP 的使用

以上面轉換後的關聯式資料表，可以滿足平台提供給使用者大多數的服務，設計的好其實也沒有太大效能的問題：
1.『作業-題目』的資料表：可以撈取每一份作業裡面包含哪些題目、包含幾題。
2.『作答記錄』：可以統計每一份作業，每位學生的作答狀況

但有些情境的JOIN 就會比較複雜，例如：學生在每個科目的作業平均正確率
撈的步驟大概是這樣（本來把SQL寫出來了，但太長了我想你也不會看，還是換成人話好了）

1. 整合題目資訊：科目 JOIN 章節 JOIN 題目
2. 抓取作業的所屬科目：『作業-題目』JOIN 題目 JOIN 章節 JOIN 科目
3. 算每包作業的題數：『作業-題目』做 COUNT
4. 算每包作業、每位學生的得分：從作答記錄做 SUM(score)
5. 整合上述四個步驟的結果：
   6. JOIN 總得分和作業題數，總得分除以作業題數，得到正確率
   7. 作業正確率 JOIN 學生資訊 JOIN 題目資訊
   8. 再以科目、每位學生為分群，平均每份作業的正確率

是不是看完就覺得非常複雜，光是資料表之間的邏輯就令人頭昏腦脹，更不用討論還有很多 SQL的語法，計算邏輯有誤區要小心。

OLTP 轉換成 OLAP

這邊做了幾個轉換:

1. 把科目、章節與題目整合成一張題目維度表
2. 把作業裡面有多少題目做成一張維度表，也可以增加更多統計資訊，例如總分
3. 作答記錄本身就可以是一張事實表，你想要的話也可以進一步先做得分加總的事實表

針對『作業題目維度表』，有幾個可以討論的點：

1. 維度表還是事實表：這裡可能會有人覺得作業題目的統計應該是事實表，這個想法也對，因為有些度量值可以看作是一個屬性，也可以看成一個可以匯總用的數值，這邊視為維度表是因為我們不會對作業的題目數量去做平均。
2. 依據產品情境做客製化，不用在意正規化：因為這邊題目只會屬於一個科目，我們也簡化一個作業裡面只會包一個科目的題目，所以你可以把科目名稱提早做進維度表。

我們再回頭看一次剛剛的問題『學生在每個科目的作業平均正確率』，就會發現我們只需要從『作答記錄事實表』去JOIN 『作業題目維度表』以及『學生維度表』，再做統計計算，就能完成統計了。

小結

今天從需求描述畫成 ERD ，然後轉成OLTP 的關聯式資料表，再進一步轉成 OLAP的 Data Cube，應該不難發現將資料表去正規化以後，能夠幫助業務、產品經理這些非數據、技術背景的夥伴，更專注在資料分析與找出價值本身，不會被很多煩雜的技術細節分心。

這也是為什麼我想多花幾天的篇幅，來深入聊聊 OLAP 的資料表設計技術，到底對於讓一個組織開始使用資料決策，能帶來多大的幫助。